package com.gukun.springboot.tdengine.dao;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONObject;
import com.gukun.springboot.tdengine.domain.common.PageResult;
import com.gukun.springboot.tdengine.domain.enums.TagType;
import com.gukun.springboot.tdengine.dto.ColumnDto;
import com.gukun.springboot.tdengine.dto.TableDto;
import com.gukun.springboot.tdengine.enums.TdengineDataType;
import com.gukun.springboot.tdengine.enums.TdengineEnums;
import com.gukun.springboot.tdengine.pojo.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.math3.dfp.DfpField;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 使用原生JDBC进行数据处理
 * 在插入等功能上，减少sql损耗
 */
public class TDEngineDao {
    private static final Logger logger = LoggerFactory.getLogger(TDEngineDao.class);

    private Connection connection;
    private Statement stmt;

    private String jdbcUrl;

    private String host;

    public Statement getStmt() {
        return stmt;
    }

    public Connection getConnection() {
        return connection;
    }

    public Connection connect() throws SQLException {
        return DriverManager.getConnection(jdbcUrl);
    }

    private int port;

    private String dbName;


    /**
     * Create Connection and Statement
     */
    public void init(String host, int port, String user, String password, String dbName) {
        //拼接数据库字符串
        var url = "jdbc:TAOS-WS://" + host + ":" + port + "/?user=" + user + "&password=" + password;
        this.dbName = dbName;
        try {
            if (url.equals(jdbcUrl)
                    && connection != null
                    && connection.isValid(500)) {
                return;
            }
        } catch (SQLException e) {
            logger.error("check error", e);
        }
        this.jdbcUrl = url;
        try {
            // get connection
            connection = connect();
            stmt = connection.createStatement();
        } catch (SQLException e) {
            logger.error("init error", e);
        }

    }


    /**
     * 获取所有数据库
     *
     * @return 数据库列表
     */
    public List<String> showDatabases() {
        String sql = " show databases";
        List<String> dbList = new ArrayList<>();
        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            while (resultSet.next()) {
                dbList.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            logger.error("showDatabases error", e);
            throw new RuntimeException(e);
        }
        return dbList;
    }


    public void dropDatabase(String dbName) throws SQLException {
        String sql = " drop database if exists " + dbName;
        executeUpdateSQL(sql);
    }


    public void createDatabase(String dbName) throws SQLException {
        String sql = " create database if not exists " + dbName + " keep  3650 duration 10 buffer 256 comp 2 wal_level 1 cachemodel 'last_value' ";
        executeUpdateSQL(sql);
    }

    public void createDatabase() throws SQLException {
        String sql = " create database if not exists " + this.dbName + " keep  3650 duration 10 buffer 256 comp 2 wal_level 1 cachemodel 'last_value' ";
        executeUpdateSQL(sql);
    }


    public void createDatabase(String dbName, Integer vgroups, Integer buffer) throws SQLException {
        String sql = " create database if not exists " + dbName + " VGROUPS " + vgroups + " buffer " + buffer + " keep  3650 duration 10 comp 2 wal_level 1 cachemodel 'last_row' ";
        executeUpdateSQL(sql);
    }

    public void createDatabase(String dbName, Integer vgroups, Integer buffer, Integer cachesize, String cachemodel) throws SQLException {
        String sql = " create database if not exists " + dbName + " VGROUPS " + vgroups + " buffer " + buffer +
                " keep  3650 duration 10 comp 2 wal_level 1 cachesize " + cachesize + " cachemodel '" + cachemodel + "' ";
        executeUpdateSQL(sql);
    }


    public void useDb(String dbName) throws SQLException {
        this.dbName = dbName;
        executeUpdateSQL("use " + dbName);
    }


    /**
     * 写入数据
     *
     * @param tagName     表名
     * @param tagDataList 数据
     * @throws SQLException 异常
     */
    public void insertData(String tagName, List<TagData> tagDataList) throws SQLException {
        for (TagData tagData : tagDataList) {
//            String sql = "insert into " + dbName + "." + tagName
//                    + " values(" + tagData.getTs() + "," + tagData.getV() + "," + tagData.getQ() + ")";
            //修改后的插入数据无质量Q
            String sql = "insert into " + dbName + "." + tagName
                    + " values(" + tagData.getTs() + "," + tagData.getV() + ")";
            executeUpdateSQL(sql);
        }
    }


    public List<TagData> getReal() throws SQLException {
        String sql = "select last_row(*),tagName from syncdb_guk.tag0  group by tagName";
        executeUpdateSQL(sql);
        return new ArrayList<>();
    }

    public TagData getRealByTableName(String tableName) throws SQLException {
        TagData tagData = new TagData();
        String sql = "select last_row(ts) as ts,last_row(v) as v from " + tableName;
        try (ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                tagData.setTs(rs.getLong(1));
                tagData.setV(rs.getString(2));
            }
        } catch (Exception ex) {
            logger.error("getRealByTableName error，sql:{}", sql, ex);
            throw new SQLException("getRealByTableName error", ex);
        }

        return tagData;
    }

    public void dropTable(String tbName) throws SQLException {
        final String sql = "drop table if exists " + this.dbName + "." + tbName;
        executeUpdateSQL(sql);
    }


    /**
     * 创建超级表
     *
     * @param table
     */
    public void createSupTable(Table table) throws Exception {
        Map<String, String> columns = table.getColumns();
        Map<String, String> tags = table.getTags();

        StringBuilder sql = new StringBuilder("create stable if not exists ");
        sql.append(this.dbName).append(".").append(table.getTableName()).append("(");
        for (Map.Entry<String, String> entry : columns.entrySet()) {
            sql.append(entry.getKey()).append(" ").append(entry.getValue()).append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(") tags(");
        for (Map.Entry<String, String> entry : tags.entrySet()) {
            sql.append(entry.getKey()).append(" ").append(entry.getValue()).append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(");");
        System.out.println(sql.toString());
        try {
            executeUpdateSQL(sql.toString());
        } catch (Exception e) {
            logger.error("创建数据库失败");
            throw e;
        }
    }

    /**
     * 采集器默认新建的超级表，字段类型与常见的存在差异
     */
    public void createSupTableAll() throws SQLException {
        for (TagType type : TagType.values()) {
            //String sql = " create table if not exists " + getSTableName(type) + "(ts timestamp, v " + type + ", q tinyint) tags(tn bigint)";
            //去掉超表里面的q，修改tn字段
            String sql = " create table if not exists "
                    + getSTableName(type) + "(ts timestamp, v " + type + ") " +
                    "tags(ti nchar(1024),tn nchar(64),ta int)";
            executeUpdateSQL(sql);
        }
    }

    /**
     * 创建普通表
     */
    public void createTable(Table table) throws Exception {
        Map<String, String> columns = table.getColumns();
        StringBuilder sql = new StringBuilder("create table if not exists ");
        sql.append(this.dbName).append(".").append(table.getTableName()).append("(");
        for (Map.Entry<String, String> entry : columns.entrySet()) {
            sql.append(entry.getKey()).append(" ").append(entry.getValue()).append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(");");
        System.out.println(sql);
        executeUpdateSQL(sql.toString());
    }


    /**
     * 创建子表
     *
     * @param supTable
     * @param tableName
     * @param tags
     */
    public void createTable(String supTable, String tableName, Map<String, Object> tags, String dbName) throws Exception {
//        CREATE TABLE d1001
//        USING meters (
//                location,
//                group_id
//        ) TAGS (
//                "California.SanFrancisco",
//                2
//        );
        StringBuilder sql = new StringBuilder("create table if not exists ");
        sql.append(dbName).append(".").append(tableName).append(" USING ")
                .append(dbName).append(".").append(supTable).append(" (");
        for (Map.Entry<String, Object> entry : tags.entrySet()) {
            sql.append(entry.getKey()).append(",");
        }
        //删除最后一个逗号
        sql.deleteCharAt(sql.length() - 1);
        sql.append(") TAGS (");
        for (Map.Entry<String, Object> entry : tags.entrySet()) {
            if (entry.getValue() instanceof String) {
                //字符串类型
                sql.append("\"").append(entry.getValue()).append("\",");
            } else {
                //数字类型
                sql.append(entry.getValue()).append(",");
            }
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(");");

        System.out.println(sql);

        try {
            executeUpdateSQL(sql.toString());
        } catch (Exception e) {
            logger.error("create table error", e);
            throw e;
        }
    }

    /**
     * 获取表格结构
     *
     * @param dbName    数据库名称
     * @param tableName 表名
     * @return Table 表格
     * @throws Exception 异常
     */
    public TableDto getTableStructure(String dbName, String tableName) throws Exception {
        StringBuilder sql = new StringBuilder("describe ").append(dbName).append(".").append(tableName);
        TableDto tableDto = new TableDto();
        try (ResultSet rs = stmt.executeQuery(sql.toString())) {
            logger.info("查询表结构:{}", sql);

            tableDto.setName(tableName);
            List<ColumnDto> columnDtoList = new ArrayList<>();
            List<ColumnDto> tagColumnDtoList = new ArrayList<>();
            tableDto.setColumnsInfo(columnDtoList);
            tableDto.setTagsInfo(tagColumnDtoList);
            while (rs.next()) {
                ColumnDto columnDto = new ColumnDto();
                columnDto.setName(rs.getString("field"));
                columnDto.setType(rs.getString("type"));
                columnDto.setLength(rs.getInt("length"));
                String note = rs.getString("note");
                TdengineDataType tdengineDataType = TdengineDataType.getTdengineDataType(columnDto.getType());
                if (tdengineDataType != null) {
                    columnDto.setIsLength(tdengineDataType.isLength());
                }
                if ("TAG".equals(note)) {
                    tagColumnDtoList.add(columnDto);
                } else {
                    columnDtoList.add(columnDto);
                }
            }
        } catch (Exception e) {
            logger.error("getTableStructure error", e);
            throw new Exception("获取表结构失败: " + e.getMessage(), e);
        }

        return tableDto;
    }

    /**
     * 批量创建表(demo)
     *
     * @param tagInfoList
     * @throws SQLException
     */
    public void createTables(List<TagInfo> tagInfoList) throws SQLException {
        for (int i = 0; i < tagInfoList.size(); ) {
            StringBuilder svSql = new StringBuilder();
            svSql.append("create table ");
            int j = 0;
            int size = tagInfoList.size();
            try(Statement stmt = this.connection.createStatement()){
                for (; j < 2000 && (i + j) < size; j++) {
                    TagInfo tagInfo = tagInfoList.get(i + j);
                    String sTableName = getSTableName(tagInfo.getTagType());

                    String newTags = tagInfo.getTags().replaceAll("\"", "-");

                    svSql.append(" if not exists ").append(tagInfo.getTagName())
                            .append(" using ").append(sTableName)
                            .append(" tags(")
                            //.append(tagInfo.getTagId())
                            //此处创建子表的tags不再是tagId，而是子表对应的信息
                            //这里的tags内容不要有双引号，有的话需要转义一下

                            .append("\"" + newTags + "\"")
                            .append(",")
                            .append("\"" + (StringUtils.isNotBlank(tagInfo.getTn()) ? tagInfo.getTn() : "notn") + "\"")
                            .append(",")
                            .append("\"" + (StringUtils.isNotBlank(tagInfo.getTa()) ? tagInfo.getTa() : "1") + "\"")
                            .append(")");
                }
                executeOperateSQL(stmt,svSql.toString());
                i += j;
            }catch (Exception e){
                logger.error("createTables error", e);
            }

        }
    }

    public void deleteTables(List<TagInfo> tagInfoList) throws SQLException {
        //drop table if exists tableName,if exists tableName
        for (int i = 0; i < tagInfoList.size(); ) {
            StringBuilder svSql = new StringBuilder();
            svSql.append("drop table ");
            try(Statement stmt = this.connection.createStatement()){
                int j = 0;
                int size = tagInfoList.size();
                for (; j < 2000 && (i + j) < size; j++) {
                    TagInfo tagInfo = tagInfoList.get(i + j);
                    if(tagInfo != null && tagInfo.getTagName() != null && !tagInfo.getTagName().isEmpty()){
                        svSql.append(" if exists ").append(tagInfo.getTagName()).append(",");
                    }
                }
                svSql = svSql.deleteCharAt(svSql.length() - 1);
                executeOperateSQL(stmt,svSql.toString());
                i += j;
            }catch (Exception e){

            }
        }
    }

    /**
     * 根据类型建立超表
     */
    public void createSupTable(TagType type) throws SQLException {
//        String sql = " create table if not exists "
//                + getSTableName(type) + "(ts timestamp,v "
//                + type + ", quality tinyint) tags(tagName varchar(128))";
        //修改创建超表的信息
        String sql = " create table if not exists "
                + getSTableName(type) + "(ts timestamp,v "
                + type + ") tags(ti nchar(1024))";
        executeUpdateSQL(sql);
    }


    /**
     * 获取超表名称
     *
     * @param type
     * @return
     */
    private String getSTableName(TagType type) {
        String stag = "";
        switch (type) {
            case DOUBLE:
            case BOOL:
            case INT:
            case FLOAT:
                stag = dbName + ".t" + type.name().toLowerCase();
                break;
        }
        return stag;
    }

    public ResultSet executeQuerySQL(String sql) throws SQLException {
        try (ResultSet resultSet = stmt.executeQuery(sql)) {
            return resultSet;
        } catch (SQLException e) {
            logger.error("Execute SQL: {}", sql);
            throw e;
        } catch (Throwable throwable) {
            logger.error("Execute SQL: {}", sql);
            throw throwable;
        }
        // 确保在使用完ResultSet后关闭它
    }

    public void executeUpdateSQL(String sql) throws SQLException {
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw e;
        } catch (Throwable throwable) {
            throw throwable;
        }
    }

    public void executeOperateSQL(Statement stmt,String sql) throws SQLException {
        try {
            stmt.execute(sql);
        } catch (SQLException e) {
            throw e;
        } catch (Throwable throwable) {
            throw throwable;
        }
    }

    /**
     * 执行SQL
     *
     * @param sql
     * @param limit
     * @return
     * @throws SQLException
     */
    public List<List<String>> executeSQL(String sql, int limit) throws SQLException {
        List<List<String>> result = new ArrayList<>();
        //jsqlparser判断是否为select语句

        try (ResultSet rs = stmt.executeQuery(sql)) {
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            List<String> columnNames = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                columnNames.add(rsmd.getColumnName(i));
            }
            result.add(columnNames);
            int count = 0;
            if (limit > 0) {
                while (rs.next() && count < limit) {
                    List<String> row = new ArrayList<>();
                    for (int i = 1; i <= columnCount; i++) {
                        row.add(rs.getString(i));
                    }
                    result.add(row);
                    count++;
                }
            } else {
                while (rs.next()) {
                    List<String> row = new ArrayList<>();
                    for (int i = 1; i <= columnCount; i++) {
                        row.add(rs.getString(i));
                    }
                    result.add(row);
                }
            }
        }catch (SQLException e){
            logger.error("executeSQL error,sql:{}",sql ,e);
            throw e;
        }
        return result;
    }

    /**
     * 获取所有超级表
     *
     * @return 超级表列表
     */
    public List<String> showSTables() {
        String sql = " show stables";
        List<String> tableList = new ArrayList<>();
        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            while (resultSet.next()) {
                tableList.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            logger.error("showSTables error", e);
        }
        return tableList;
    }


    /**
     * 根据超级表获取所有表
     *
     * @param stableName
     * @return 表列表
     */
    public List<String> showTablesBySTable(String stableName) {
        //不加distinct，如果表里面没数据，会查不出表名。如果表里面有数据，会查出重复的表名
        String sql = " select distinct(tbname) from " + stableName;
        List<String> tableList = new ArrayList<>();
        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            while (resultSet.next()) {
                tableList.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            logger.error("showTablesBySTable error", e);
        }
        return tableList;
    }

    /**
     * 获取所有表
     *
     * @return 表列表
     */
    public List<String> showTables(String filter) {
        String sql = " show tables";
        if (!StrUtil.isEmpty(filter)) {
            sql = " show tables like '%" + filter + "%'";
        }
        List<String> tableList = new ArrayList<>();
        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            while (resultSet.next()) {
                tableList.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            logger.error("showTables error", e);
        }
        return tableList;
    }

    public PageResult<TableDto> getStableByPage(String filterName, String dbName, int pageSize, int currentPage) throws Exception {
        //select * from INFORMATION_SCHEMA.ins_stables where db_name = 'zcy' order by create_time desc limit 100;
        //select count(*) from INFORMATION_SCHEMA.ins_stables where db_name = 'zcy';
        //select count(*) from INFORMATION_SCHEMA.ins_stables where db_name = 'gukun_test' limit 100 offset 100
        StringBuilder selectSql = new StringBuilder(String.format("select * from INFORMATION_SCHEMA.ins_stables where db_name = '%s'", dbName));
        StringBuilder countSql = new StringBuilder(String.format("select count(*) from INFORMATION_SCHEMA.ins_stables where db_name = '%s'", dbName));
        if (StringUtils.isNotBlank(filterName)) {
            selectSql.append(" and stable_name like '%").append(filterName).append("%'");
            countSql.append(" and stable_name like '%").append(filterName).append("%'");
        }
        // 增加分页能力
//        selectSql.append(" limit ").append(pageSize).append(" offset ").append(pageSize * (currentPage - 1));
        selectSql.append(" order by create_time desc ");
        //分页查询
//        int offset = pageSize * (currentPage - 1);
//        selectSql.append(offset).append(",").append(pageSize);
        PageResult<TableDto> page = new PageResult<>();
        List<TableDto> tableNames = new ArrayList<>();
        page.setList(tableNames);
        try (Statement statement = connection.createStatement();
             ResultSet resultSetCount = statement.executeQuery(countSql.toString())) {
            while (resultSetCount.next()) {
                page.setTotal(resultSetCount.getLong("count(*)"));
            }
            try (ResultSet resultSetSelect = statement.executeQuery(selectSql.toString())) {
                while (resultSetSelect.next()) {
                    TableDto tableDto = new TableDto();
                    tableDto.setName(resultSetSelect.getString("stable_name"));
                    tableDto.setType("SUPER_TABLE");
                    tableNames.add(tableDto);
                }
            }
        }catch (Exception ex){
            logger.error("getStableByPage error,SQL:{}",selectSql ,ex);
            throw ex;
        }
        return page;
    }

    /**
     * 根据数据库名称、表名称、分页信息查询表格
     *
     * @param filterName  搜索名称
     * @param dbName      数据库名称
     * @param pageSize    分页数量
     * @param currentPage 当前页数
     * @return 返回符合条件表名
     * @throws Exception 执行异常
     */
    public PageResult<TableDto> getTableByPage(String filterName, String likeDesc, String dbName, String superTableName, int pageSize, int currentPage, boolean needLastValue) throws Exception {
        //select * from information_schema.ins_tags as a where db_name='test2' order by tag_name asc
        //select count(*) from information_schema.ins_tags as a where db_name='test2'
        StringBuilder selectSql = new StringBuilder(String.format("select * from information_schema.ins_tags as a where db_name='%s' and tag_name = 'ti'", dbName));
        StringBuilder countSql = new StringBuilder(String.format("select count(*) from information_schema.ins_tags as a where db_name='%s' and tag_name = 'ti'", dbName));
        if (StringUtils.isNotBlank(filterName)) {
            selectSql.append(" and table_name like '%").append(filterName).append("%'");
            countSql.append(" and table_name like '%").append(filterName).append("%'");
        }
        if (StringUtils.isNotBlank(likeDesc)) {
            selectSql.append(" and tag_value like '%").append(likeDesc).append("%'");
            countSql.append(" and tag_value like '%").append(likeDesc).append("%'");
        }
        if (StringUtils.isNotBlank(superTableName)) {
            selectSql.append(" and stable_name = '").append(superTableName).append("'");
            countSql.append(" and stable_name = '").append(superTableName).append("'");
        }
        selectSql.append(" order by table_name limit ");
        //分页查询
        int offset = pageSize * (currentPage - 1);
        selectSql.append(offset).append(",").append(pageSize);
        PageResult<TableDto> page = new PageResult<>();
        List<TableDto> tableNames = new ArrayList<>();
        page.setList(tableNames);
        try (Statement statement = connection.createStatement();
             ResultSet resultSetCount = statement.executeQuery(countSql.toString())) {
            if (resultSetCount.next()) {
                page.setTotal(resultSetCount.getLong("count(*)"));
            }
            try (ResultSet resultSetSelect = statement.executeQuery(selectSql.toString())) {
                while (resultSetSelect.next()) {
                    TableDto tableDto = new TableDto();
                    tableDto.setName(resultSetSelect.getString("table_name"));
                    tableDto.setType("CHILD_TABLE");
                    tableDto.setSupName(resultSetSelect.getString("stable_name"));
                    String tagValue = resultSetSelect.getString("tag_value");
                    try {
                        JSONObject jsonObject = JSONObject.parseObject(tagValue);
                        tableDto.setDesc(jsonObject.getString("tagDesc"));
                    } catch (Exception e) {
                        tableDto.setDesc("");
                    }
                    tableNames.add(tableDto);
                }
            }
        }
        //获取最近一次的值和最近一次的时间
        if (needLastValue) {
            for (TableDto tableDto : tableNames) {
                List<String> result = getLastOneRowData(dbName, tableDto.getName());
                if (!result.isEmpty()) {
                    tableDto.setLastTime(result.getFirst());
                    tableDto.setLastValue(result.getLast());
                }
            }
        }
        return page;
    }

    public Map<String, String> getTableDescOrLikeDesc(String dbName, List<String> tableNames, boolean isLikeDesc, String likeName) {
        String sql = "SELECT table_name,tag_value FROM information_schema.ins_tags where db_name = '%s' and table_name in (%s)";
        String likeSql = "SELECT table_name,tag_value FROM information_schema.ins_tags where db_name = '%s' and tag_value like '%s'";
        HashMap<String, String> tableMap = new HashMap<>();
        try {
            String executeSql = null;
            if (isLikeDesc) {
                executeSql = String.format(likeSql, dbName, "%" + likeName + "%");
            } else {
                StringBuilder sqlBuilder = new StringBuilder();
                for (String tableName : tableNames) {
                    sqlBuilder.append("'").append(tableName).append("'");
                    sqlBuilder.append(",");
                }
                sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
                executeSql = String.format(sql, dbName, sqlBuilder);
            }
            try (PreparedStatement preparedStatement = connection.prepareStatement(executeSql);
                 ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    String tableName = resultSet.getString("table_name");
                    String tagValue = resultSet.getString("tag_value");
                    if (StringUtils.isNotBlank(tagValue)) {
                        try {
                            JSONObject jsonObject = JSONObject.parseObject(tagValue);
                            tableMap.put(tableName, jsonObject.getString("tagDesc"));
                        } catch (Exception e) {
                            tableMap.put(tableName, "");
                        }
                    }
                }
            }
        } catch (Exception e) {
            logger.error("getTableDesc error", e);
        }
        return tableMap;
    }

    /**
     * 查询历史数据
     *
     * @param queryHisDataInput
     * @return
     */
    public PageResult<TagData> queryHistoryData(QueryHisDataInput queryHisDataInput) {

        String step = queryHisDataInput.getStep();
        if ("0".equals(step)) {
            return queryOriginalHistoryData(queryHisDataInput);
        }

        //查询有step的
        //example:
        //select _wstart, first (val)  from syncdb_guk.tag10000
        //where ts > "2021-02-01 09:46:14" and ts < "2021-02-02 09:46:14" INTERVAL(300s) FILL(LINEAR);
        DateTime start = new DateTime(queryHisDataInput.getStart());
        DateTime end = new DateTime(queryHisDataInput.getEnd());
        //这边要把时间往前推一个step，避免第一个数据点丢失
        start = DateUtil.offsetSecond(start, -Integer.parseInt(step));
        end = DateUtil.offsetSecond(end, -Integer.parseInt(step));
        //_wstart as ts, first (v) as val
        //example:select _wstart as ts,first(v) as v from gukun_test.tag_0 where ts >= 1761926400000 and ts < 1762099199000 INTERVAL(30s,auto) FILL(PREV) order by _wstart asc limit 500 offset 0
        //往前推一个step后查询语句，使用_wend作为时间戳，last为前一个值，interval第二个参数为auto
        //select _wend,last(v) as v  from gukun_test.tag_0  where ts >= 1761926400000 and ts < 1762099199000 INTERVAL(5s,auto) FILL(PREV)   order by _wstart;
        String allDataSql = "select _wend, first(v) as v  from " + queryHisDataInput.getDbConfig().getDbName() + "." + queryHisDataInput.getName()
                + " where ts >= " + start.getTime() + " and ts < " + end.getTime()
                + " INTERVAL(" + step + "s,auto)  FILL(" + queryHisDataInput.getFill() + ")"
                + " order by _wstart " + queryHisDataInput.getOrderBy();

        String sql = allDataSql + " limit " + queryHisDataInput.getPageSize() + " offset " + queryHisDataInput.getPageSize() * (queryHisDataInput.getPageNum() - 1);

        logger.info("sql:{}", sql);
        String getCountSql = "select count(*) from (" + allDataSql + ")";
        logger.debug("queryHistoryData sql: {}", sql);
        logger.debug("queryHistoryData getCountSql: {}", getCountSql);

        try (ResultSet rsCount = stmt.executeQuery(getCountSql)) {
            rsCount.next();
            int count = rsCount.getInt(1);

            try (ResultSet rs = stmt.executeQuery(sql)) {
                List<TagData> tagHisDataList = new ArrayList<>();
                while (rs.next()) {
                    TagData tagHisData = new TagData();
                    tagHisData.setTs(rs.getLong(1));
                    String v = rs.getString(2);
                    try{
                        //可能存在科学计数法，将值转化为保留四位小数的字符串
                        BigDecimal bigDecimal = BigDecimal.valueOf(Double.parseDouble(v)).setScale(4);
                        tagHisData.setV(com.gukun.springboot.tdengine.util.StringUtils.removeTrailingZeros(bigDecimal.toString()));
                    }catch (Exception e){
                        tagHisData.setV(v);
                    }
                    tagHisDataList.add(tagHisData);
                }
                long d3 = System.currentTimeMillis();

                //分页塞入数据
                PageResult<TagData> result = new PageResult<>();
                result.setTotal((long) count);
                result.setList(tagHisDataList);

                return result;
            }
        } catch (SQLException e) {
            logger.error("queryHistoryData error", e);
        }

        return null;
    }

    /**
     * 查询原始历史数据(step为0)
     *
     * @param queryHisDataInput
     * @return
     */
    public PageResult<TagData> queryOriginalHistoryData(QueryHisDataInput queryHisDataInput) {

        DateTime start = new DateTime(queryHisDataInput.getStart());
        DateTime end = new DateTime(queryHisDataInput.getEnd());

        String sql = "select * from " + queryHisDataInput.getName()
                + " where ts >= " + start.getTime() + " and ts < " + end.getTime()
                + " "
                + " order by ts " + queryHisDataInput.getOrderBy()
                + " limit " + queryHisDataInput.getPageSize() + " offset " + queryHisDataInput.getPageSize() * (queryHisDataInput.getPageNum() - 1);


        String getCountSql = "select count(*) from " + queryHisDataInput.getName()
                + " where ts >= " + start.getTime() + " and ts < " + end.getTime();

//        System.out.println(sql);
//        System.out.println(getCountSql);

        try (ResultSet rsCount = stmt.executeQuery(getCountSql)) {
            rsCount.next();
            int count = rsCount.getInt(1);

            try (ResultSet rs = stmt.executeQuery(sql)) {
                List<TagData> tagHisDataList = new ArrayList<>();
                while (rs.next()) {
                    TagData tagHisData = new TagData();
                    tagHisData.setTs(rs.getLong(1));
//                    tagHisData.setV(rs.getString(2));
                    //            tagHisData.setQuality(Byte.valueOf(rs.getString(3)));
                    String v = rs.getString(2);
                    try{
                        //可能存在科学计数法，将值转化为保留四位小数的字符串
                        BigDecimal bigDecimal = BigDecimal.valueOf(Double.parseDouble(v)).setScale(4);
                        tagHisData.setV(com.gukun.springboot.tdengine.util.StringUtils.removeTrailingZeros(bigDecimal.toString()));
                    }catch (Exception e){
                        tagHisData.setV(v);
                    }
                    tagHisDataList.add(tagHisData);
                }
                //分页塞入数据
                return new PageResult<>(tagHisDataList, (long) count);
            }
        } catch (SQLException e) {
            logger.error("queryHistoryData error", e);
        }
        return null;
    }

    /**
     * 给超表添加列或者标签
     * ALTER STABLE stb_name ADD COLUMN col_name column_type
     * ALTER STABLE stb_name ADD TAG tag_name tag_type
     *
     * @param tableName 表名
     * @param dbName    数据库名
     * @param columnVo  字段信息
     * @param type      COLUMN 字段 TAG 标签
     * @throws Exception 异常
     */
    public void addColumnSTable(String tableName, String dbName, ColumnVo columnVo, TdengineEnums type) throws Exception {
        StringBuilder addColumnSql = new StringBuilder();
        addColumnSql.append(String.format("ALTER STABLE %s.%s ADD %s %s ", dbName, tableName, type.getName(), columnVo.getNewName()));
        if (columnVo.getIsLength()) {
            addColumnSql.append(columnVo.getType()).append("(").append(columnVo.getLength()).append(")");
        } else {
            addColumnSql.append(columnVo.getType());
        }
        executeQuerySQL(addColumnSql.toString());
    }

    /**
     * 超级表删除字段
     * ALTER STABLE stb_name DROP COLUMN col_name
     * ALTER STABLE stb_name DROP TAG tag_name
     */
    public void dropColumnSTable(String tableName, String dbName, ColumnVo columnVo, TdengineEnums type) throws Exception {
        executeQuerySQL(String.format("ALTER STABLE %s.%s DROP %s %s", dbName, tableName, type.getName(), columnVo.getName()));
    }

    /**
     * 超级表修改列宽
     * ALTER STABLE stb_name MODIFY COLUMN col_name data_type(length)
     * ALTER STABLE stb_name MODIFY TAG tag_name data_type(length)
     */
    public void modifyColumnSTable(String tableName, String dbName, ColumnVo columnVo, TdengineEnums type) throws Exception {
        executeQuerySQL(String.format("ALTER STABLE %s.%s MODIFY %s %s %s(%s)", dbName, tableName, type.getName(), columnVo.getName(), columnVo.getType(), columnVo.getNewLength()));
    }

    /**
     * 标签名称重命名
     * ALTER STABLE stb_name RENAME TAG old_tag_name new_tag_name
     */
    public void modifyTagNameSTable(String tableName, String dbName, ColumnVo columnVo) throws Exception {
        executeQuerySQL(String.format("ALTER STABLE %s.%s RENAME TAG %s %s", dbName, tableName, columnVo.getName(), columnVo.getNewName()));
    }

    /**
     * 普通表新增列
     * ALTER TABLE tb_name ADD COLUMN field_name data_type
     *
     * @param tableName 表名
     * @param dbName    数据库名
     * @param columnVo  字段信息
     * @throws Exception 异常
     */
    public void addColumnTable(String tableName, String dbName, ColumnVo columnVo) throws Exception {
        StringBuilder columnType = new StringBuilder();
        if (columnVo.getIsLength()) {
            columnType.append(columnVo.getType()).append("(").append(columnVo.getNewLength()).append(")");
        } else {
            columnType.append(columnVo.getType());
        }
        executeQuerySQL(String.format("ALTER TABLE %s.%s ADD COLUMN %s %s", dbName, tableName, columnVo.getNewName(), columnType));
    }

    /**
     * 删除普通表列
     * ALTER TABLE tb_name DROP COLUMN field_name;
     */
    public void dropColumnTable(String tableName, String dbName, ColumnVo columnVo) throws Exception {
        executeQuerySQL(String.format("ALTER TABLE %s.%s DROP COLUMN %s", dbName, tableName, columnVo.getName()));
    }

    /**
     * 修改普通表列宽
     * ALTER TABLE tb_name MODIFY COLUMN field_name data_type(length);
     */
    public void modifyColumnTable(String tableName, String dbName, ColumnVo columnVo) throws Exception {
        executeQuerySQL(String.format("ALTER TABLE %s.%s MODIFY COLUMN %s %s", dbName, tableName, columnVo.getName(), "(" + columnVo.getNewLength() + ")"));
    }

    /**
     * 修改普通表列名
     * ALTER TABLE tb_name RENAME COLUMN old_col_name new_col_name
     */
    public void modifyColumnNameTable(String tableName, String dbName, ColumnVo columnVo) throws Exception {
        executeQuerySQL(String.format("ALTER TABLE %s.%s RENAME COLUMN %s %s", dbName, tableName, columnVo.getName(), columnVo.getNewName()));
    }

    public List<String> getLastOneRowData(String dbName, String tableName) throws Exception {
        String executeSql = String.format("select last(*) from %s.%s", dbName, tableName);
        List<String> dataList = new ArrayList<>();
        try (ResultSet rs = stmt.executeQuery(executeSql)) {

            ResultSetMetaData metaData = rs.getMetaData();
            int valueType = metaData.getColumnType(2);
            while (rs.next()) {
                dataList.add(DateUtil.format(rs.getDate(1), "yyyy-MM-dd HH:mm:ss"));
                if (valueType == Types.DOUBLE || valueType == Types.FLOAT) {
                    String v = String.valueOf(rs.getDouble(2));
                    // 转换为 BigDecimal，并保留最多6位小数（四舍五入）
                    BigDecimal bd = new BigDecimal(v)
                            .setScale(6,4)
                            .stripTrailingZeros(); // 去掉末尾的0

                    // 如果是整数（比如 123.000000 -> 123），则不显示小数点
                    String result = bd.scale() < 0 ? bd.toPlainString() : bd.stripTrailingZeros().toPlainString();

                    dataList.add(result);
                } else {
                    dataList.add(rs.getString(2));
                }
            }
        }
        return dataList;
    }

    public List<List<String>> getDNodesInfo() throws Exception {
        return executeSQL("SELECT * FROM information_schema.ins_dnodes;", 0);
    }

    public List<List<String>> getMNodesInfo() throws Exception {
        return executeSQL("SELECT * FROM information_schema.ins_mnodes;", 0);
    }

    public List<List<String>> getQNodesInfo() throws Exception {
        return executeSQL("SELECT * FROM information_schema.ins_qnodes;", 0);
    }

    public void addDNode(String dnodeEndpoint) throws Exception {
        executeUpdateSQL("CREATE DNODE \"" + dnodeEndpoint + "\"");
    }

    public void dropDNode(String dNodeId) throws Exception {
        executeUpdateSQL("DROP DNODE " + dNodeId + " force");
    }

    public void addMNode(String dNodeId) throws Exception {
        executeUpdateSQL("CREATE MNODE ON DNODE " + dNodeId);
    }

    public void dropMNode(String dNodeId) throws Exception {
        executeUpdateSQL("DROP MNODE ON DNODE " + dNodeId);
    }

    public void addQNode(String dNodeId) throws Exception {
        executeUpdateSQL("CREATE QNODE ON DNODE " + dNodeId);
    }

    public void dropQNode(String dNodeId) throws Exception {
        executeUpdateSQL("DROP QNODE ON DNODE " + dNodeId);
    }

    public List<List<String>> getSlowSqlCountAndData(java.util.Date startTime, java.util.Date endTime, float consumeTimeMin, float consumeTimeMax, boolean isDistinct, int limit, int offset) throws Exception {
        String countSql = "select count(*) from (%s)";
        String childSelectSql = getSlowSqlStr(startTime, endTime, consumeTimeMin, consumeTimeMax, isDistinct);
        String limitChildSelectSql = childSelectSql + " limit " + offset + "," + limit;
        String couSql = String.format(countSql, childSelectSql);
        List<List<String>> countResult = executeSQL(couSql, 0);
        List<List<String>> dataResult = executeSQL(limitChildSelectSql, 0);
        countResult.addAll(dataResult);
        return countResult;
    }

    private static String getSlowSqlStr(java.util.Date startTime, java.util.Date endTime, float consumeTimeMin, float consumeTimeMax, boolean isDistinct) {
        StringBuilder childSelectSql = new StringBuilder();
        if (isDistinct) {
            childSelectSql.append("SELECT LAST_ROW(start_ts) as start_ts,");
        } else {
            childSelectSql.append("SELECT start_ts,");
        }
        childSelectSql.append("db, ip, `user`, sql, query_time, rows_num FROM log.taos_slow_sql_detail WHERE ");
        if (startTime != null && endTime != null) {
            childSelectSql.append("start_ts > %s AND start_ts <= %s AND ");
        }
        childSelectSql.append("query_time >= %s%s");
        if (isDistinct) {
            childSelectSql.append("PARTITION by sql,db ORDER BY start_ts DESC");
        } else {
            childSelectSql.append("ORDER BY start_ts DESC");
        }
        float minValue = consumeTimeMin * 1000;
        float maxValue = consumeTimeMax * 1000;
        String maxValueStr = null;
        if (minValue < maxValue) {
            maxValueStr = " and " + maxValue + " ";
        } else {
            maxValueStr = " ";
        }
        return String.format(childSelectSql.toString(), startTime.getTime(), endTime.getTime(), minValue, maxValueStr);
    }

    public List<List<String>> getStatisticSlowSqlInfoByPage(
            java.util.Date startTime,
            java.util.Date endTime,
            int limit, int offset) throws Exception {
        String countSql = "select count(*) from (%s)";
        StringBuilder dataSql = new StringBuilder("SELECT sql, db, count(*) as query_count, cast(avg(query_time) as int) as avg_query_time, max(query_time) as max_query_time, cast(avg(rows_num) as int) as avg_rows_num, max(rows_num) as max_rows_num FROM log.taos_slow_sql_detail ");
        if (startTime != null && endTime != null) {
            dataSql.append("WHERE start_ts > %s AND start_ts <= %s");
        }
        dataSql.append(" PARTITION BY sql, db");
        String executeDataSql = null;
        if (startTime != null && endTime != null) {
            executeDataSql = String.format(dataSql.toString(), startTime.getTime(), endTime.getTime());
        } else {
            executeDataSql = dataSql.toString();
        }
        String executeCountSql = String.format(countSql, executeDataSql);
        executeDataSql = executeDataSql + " SLIMIT " + offset + "," + limit;
        List<List<String>> countData = executeSQL(executeCountSql, 0);
        List<List<String>> data = executeSQL(executeDataSql, 0);
        countData.addAll(data);
        return countData;
    }

    /**
     * 删除指定表格的过期数据
     */
    public void deleteDataByPeriod(String dbName, String tableName, long timestamp) throws SQLException {
        String deleteSql = String.format("DELETE FROM %s.%s WHERE ts < %s", dbName, tableName, timestamp);
        stmt.execute(deleteSql);
    }

    public ResultSet executeQuery(String searchDesc) throws SQLException {
        return stmt.executeQuery(searchDesc);
    }
}





